Аналитика в Яндекс.Афише
Внимание! Это учебный проект, в проекте не доступен перезапуск ячеек. Датасеты Яндекса защищены соглашением о неразглашении.
Описание проекта¶
Общее задание: дать рекомендации маркетологам о том как оптимизировать расходы на рекламу
В распоряжении были данные от Яндекс.Афиши за 2017-2018 года (промежуток в 1 год), это 3 таблицы: visits с информацией о посещениях сайта, orders с информацией о заказах и costs с информацией о затратах на маркетинг
Проект отвечает на вопросы: как посетители сайта пользуются им, как быстро начинают покупать, сколько тратит каждый клиент, когда клиент окупается.
Шаг 1. Загрузка и подготовка данных
Импортируем библиотеки для работы
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from scipy import stats as st
import math as mt
import seaborn as sns
import datetime as dt
from pylab import rcParams
rcParams['figure.figsize'] = 16, 8
pd.options.mode.chained_assignment = None
#здесь мы загружаем датасеты из csv в pandas, но т.к. они защищены соглашением о неразглашении, перезапуск этого процесса не доступен
#visits_log = pd.read_csv('visits_log.csv')
#orders_log = pd.read_csv('orders_log.csv')
#costs_log = pd.read_csv('costs.csv')
Рассмотрим первый датасет, предположительно, с везитами на сайт Яндекс.Афиши - visits_log
visits_log.head()
Нужно привести названия столбцов к нижнему регистру и добавить "_" там где это необходимо
visits_log.info()
Перевести столбцы End Ts и Start Ts к типу данных datetime
visits_log.describe()
Судя по Source Id всего источников 10, и большая часть трафика распределена между 3,4 и 5 источниками. Далее нужно будет изучить это подробнее
visits_log.duplicated().value_counts()
Дубликатов нет
Внимание, сообщение из будущего: при проверке длительности сессий в этой таблице было обнаружено, что длительности округлены до 1 минуты, и многие из них в меньшую сторону (т.е. 0 минут), но в самом конце датасета обнаружены 19-секундные сессии. Осмелюсь предположить, что 19-секундные сессии образовлись в момент снятия статистики, т.е. это как бы прерванные сессии людей (срез в момент выгрузки) или иная ошибка.
Я думаю можно округлить эти строки, причём в большую сторону, т.к. вероятность, что они пробыли на сайте больше 19 велика. В любом случае этих строк менее 1% от всех строк поэтому их изменение не повредит выводам.
orders_log.head()
Здесь так же нужно привести названия столбцов к нижнему регистру и добавить "_" там где это необходимо
orders_log.info()
Перевести столбец Buy Ts к типу данных datetime
orders_log.describe()
Есть большие выбросы в столбце Revenue, учитывая, что 75% трафика до 4,89, а максимальый доход с одного заказа 2633,28 (долларов?)
orders_log.duplicated().value_counts()
Дубликатов нет
costs_log.head()
costs_log.info()
Нужно поменять тип данных столбца dt
costs_log.describe()
Максимальное значение costs сильно выделяется на фоне 75% меньших значений, скорее всего тут тоже выбросы
costs_log.duplicated().value_counts()
Дубликатов нет
Переименуем столбцы¶
Т.к. столбцы содержат не только верхний регистр, но и пробелы, я решил, что быстрее будет просто вручную переименовать их и не тратить время на нагромаждения вокруг простой задачи.
visits_log.columns = ['device','end_ts','source_id','start_ts','uid']
visits_log.head()
orders_log.columns = ['buy_ts','revenue','uid']
orders_log.head()
Поменяем типы данных¶
visits_log['end_ts'] = pd.to_datetime(visits_log['end_ts'])
visits_log['start_ts'] = pd.to_datetime(visits_log['start_ts'])
visits_log.info()
orders_log['buy_ts'] = pd.to_datetime(orders_log['buy_ts'])
orders_log.info()
costs_log['dt'] = pd.to_datetime(costs_log['dt'])
costs_log.info()
Округлим даты¶
Теперь округлим последние строки датасета, где кончание сессии (столбец end_ts) не округлено и оканчивается на 19 секунд
visits_log['end_ts'] = visits_log['end_ts'].dt.ceil('1T')
visits_log
Вывод: В целом данные достаточно полноценные для анализа, преобразований пришлось делать не много, теперь можно анализировать
Шаг 2. Построение отчётов и подсчёт метрик
В данном шаге ответы на ключевые вопросы от команды маркетологов
Сколько людей пользуются в день, неделю, месяц?
Для расчёта нужно выделить день, неделю и месяц посещения, затем создать сгруппированные датасеты с уникальными пользователями по этим промежуткам времени:
visits_log['day'] = visits_log['start_ts'].astype('datetime64[D]')
visits_log['week'] = visits_log['start_ts'].astype('datetime64[W]')
visits_log['month'] = visits_log['start_ts'].astype('datetime64[M]')
visits_log_daily = visits_log.groupby(['day']).agg({'uid':'nunique'})
visits_log_weekly = visits_log.groupby(['week']).agg({'uid':'nunique'})
visits_log_monthly = visits_log.groupby(['month']).agg({'uid':'nunique'})
print(
"В среднем в день сайт посещают:",
round(visits_log_daily['uid'].mean()),
"человек (DAU)\nВ среднем в неделю сайт посещают:",
round(visits_log_weekly['uid'].mean()),
"человек (WAU)\nВ среднем в месяц сайт посещают:",
round(visits_log_monthly['uid'].mean()),
"человек (MAU)"
)
Посмотрим какой разброс посещаемости в рамках дня, недели, месяца
color = {'boxes': 'DarkGreen', 'whiskers': 'DarkOrange', 'medians': 'DarkBlue', 'caps': 'Red'}
fig, axes = plt.subplots(1, 3, figsize=(16, 10))
visits_log_daily['uid'].plot.box(title='Разброс дневной посещаемости', grid=True, ax=axes[0], color=color)
visits_log_weekly['uid'].plot.box(title='Разброс недельной посещаемости', grid=True, ax=axes[1], color=color)
visits_log_monthly['uid'].plot.box(title='Разброс месячной посещаемости', grid=True, ax=axes[2], color=color)
plt.show()
И ещё интересно как посещаемость сайта изменяется на протяжении всего времени, по дням
visits_log_daily.plot.line(title="График изменения посещвемости уникальными пользователями по дням", grid=True, figsize=(16, 6))
plt.ylabel('Количество уникальных пользователей')
plt.xlabel('Время')
plt.show()
Видно, что посещаемость с наступлением осени 2017 начала растри, и даже был необычный пик:
visits_log_daily[visits_log_daily.index=='2017-11-24']
24 ноября 2017 - много людей, много раз посещали сайт, видимо там было какое-то яркое событие, которое широко рекламировалось.
Я воспользовался сервисом https://web.archive.org и выяснил, что там была черная пятница и видимо распродажа билетов на Ozzy Osborn'a в Олимпийском или Guns'n'Roses в Крокус Сити Холл.
А весной посещаемость начала падать, видимо это сезонный процесс, зимой больше людей интересуются концертами и выступлениями, а летом отдыхают на даче и на море. Есть ещё один необычный день, 31 марта 2018:
visits_log_daily[visits_log_daily.index=='2018-03-31']
Почему-то в этот сайт посетил всего один человек, это точно ошибка, возможно сайт был на реконструкции в этот день. Так же есть взлет посещаемости к 31 мая 2018:
visits_log_daily[visits_log_daily.index>='2018-05-29']
Возможно там тоже было широко рекламируемое событие, премьера. В этот день празднуется "Всемирный день без табака" и "Всемирный день блондинок", но не думаю, что этот всплеск как-то связан с этим
(visits_log
.pivot_table(index='week', columns='source_id', values='uid', aggfunc='nunique')
.plot(title='Посетители по источникам', grid=True)
)
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 6','Источник 7','Источник 9','Источник 10'])
plt.ylabel('Количество уникальных пользователей')
plt.xlabel('Время')
plt.show()
С источников 3 и 4 приходит больше новых (уникальных) пользователей и на протяжении всего времени трафик уникальных пользователей по ним преобладает, особенно в холодные месяцы года.
(visits_log
.pivot_table(index='day', columns='device', values='uid', aggfunc='nunique')
.plot(title='Посетители по устройствам', grid=True, color=['darkgreen','brown'])
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('Количество уникальных пользователей')
plt.xlabel('Время')
plt.show()
Преобладает ПК, оттуда приходит больше уникальных пользователей. На графике видно, сильная разность трафика внтури недели по desktop-у, в будни люди заходят на работе в минуты отдыха, а на выходных только из дома. С touch-устройств трафик более стабильный.
В холодные месяцы пользователей с ПК становится больше (все вернулись с отпусков на работу). Опять же виден всплеск в черную пятницу.
source_df = (visits_log
.query('(source_id != 6) and (source_id != 7)')
.groupby('source_id')
.agg({'uid':'nunique'})
.reset_index()
)
source_and_device_df = (visits_log
.query('(source_id != 6) and (source_id != 7)')
.groupby(['source_id','device'])
.agg({'uid':'nunique'})
.reset_index()
.pivot_table(index='source_id', columns='device', values='uid', aggfunc='sum')
)
ax = sns.barplot(x='source_id', y='uid', palette="ch:.1", data=source_df)
source_and_device_df.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
plt.title('Количество пользователей по источникам и устройствам')
plt.ylabel('Количество уникальных пользователей')
plt.xlabel('Источники')
plt.show()
В этом и на последующих подобных графиках: на переднем плане сравнение desktop и touch-трафика (т.е. ПК и мобильного), а на заднем плане — тот же показатель, без разбиения на устройства.¶
Там где суммарное количество едениц - задний столбец будет суммой desktop+touch, а там где среднее число - средним между desktop и touch¶
Здесь мы явно видим какие каналы привлекают больше уникальных пользователей, и на сколько по каждому каналу ПК-трафик преобладает над мобильным. Особая разница по 3-му источнику.
Сколько сессий в день?
print(f'В среднем {round(visits_log.groupby("day")["start_ts"].count().mean())} сессий в день')
print(f'В среднем {round(visits_log.groupby(["day", "uid"])["start_ts"].count().mean(), 2)} сессий на одного пользователя')
(visits_log
.groupby("day")[["start_ts"]]
.count()
.plot.line(title='Количество сессий по дням', grid=True, figsize=(16, 6), color='green')
)
plt.ylabel('Количество сессий')
plt.xlabel('Время')
plt.show()
Видно всё те же самые пики, что и в статистике по уникальным пользователям, но интересно ещё узнать сколько сессий приходится на одного пользователя и как изменяется этот показатель с течением времени:
sessions_count = visits_log.groupby(["day", "uid"])[["start_ts"]].count().reset_index()
sessions_means = sessions_count.groupby("day")[["start_ts"]].mean()
sessions_means = sessions_means.rename(columns = {'start_ts':'sessions_mean'})
sessions_means.plot.line(title='Среднее количество сессий на пользователя по дням', grid=True, figsize=(16, 6), color='orange')
plt.ylabel('Среденедневное количество сессий')
plt.xlabel('Время')
plt.show()
В целом за год среднее количество сессий на одного пользователя колеблется между 1.05 и 1.10 (т.е. можно предположить, что каждый 10 пользователь посещает сайт 2 раза в день), но есть тот самый пик в черную пятницу, в котором пользователи особо активно обновляли страницы сайта, и посещали по несколько раз в день. Т.е. если раньше можно было предположить, что это разные пользователи пришли на сайт одновременно, то сейчас можно утверждать, что это одни и те же люди посещали сайт в поисках скидок.
(visits_log
.groupby(['source_id','week'])['start_ts']
.count()
.reset_index()
.pivot_table(index='week', columns='source_id', values='start_ts', aggfunc='sum')
.plot(title='Сессии по источникам', grid=True)
)
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 6','Источник 7','Источник 9','Источник 10'])
plt.ylabel('Количество сессий')
plt.xlabel('Время')
plt.show()
Сессии повторяют картину по уникальных пользователям. С небольшой разницей по 1 и 2 источнику, с них чаще возвращаются.
(visits_log
.groupby(['device','day'])['start_ts']
.count()
.reset_index()
.pivot_table(index='day', columns='device', values='start_ts', aggfunc='sum')
.plot(title='Сессии по устройствам', grid=True, color=['darkgreen','brown'])
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('Количество сессий')
plt.xlabel('Время')
plt.show()
Видно, что пик в чёрную пятницу выше, люди активно возвращались и обновляли сайт
source_df = (visits_log
.query('(source_id != 6) and (source_id != 7)')
.groupby('source_id')
.agg({'start_ts':'count'})
.reset_index()
)
source_and_device_df = (visits_log
.query('(source_id != 6) and (source_id != 7)')
.groupby(['source_id','device'])
.agg({'start_ts':'count'})
.reset_index()
.pivot_table(index='source_id', columns='device', values='start_ts', aggfunc='sum')
)
ax = sns.barplot(x='source_id', y='start_ts', palette="ch:.3", data=source_df)
source_and_device_df.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
plt.title('Количество сессий по источникам и устройствам')
plt.ylabel('Количество сессий')
plt.xlabel('Источники')
plt.show()
В сравнении с трафиком уникальных пользователей, здесь подросли показатели по 1 и 2 источнику, как я и писал выше, по этим источникам возвращаются чаще, и возможно даже те, кто изначально пришёл по другому источнику. Так же возросла разница между ПК и мобильными, видимо удобнее смотреть сайт с ПК и люди переходят.
Сколько длится одна сессия?
Забегая вперед, обнаружил строки, в которых люди вышли с сайта раньше, чем зашли :) Возможно у них сменился часовой пояс, например они вошли на сайт в одном городе, а вышли уже в другом (где часовой пояс -1 час):
visits_log[visits_log['end_ts'] < visits_log['start_ts']]
Удалим их, чтоб не вызывали аномалии в статистике
visits_log = visits_log.drop(index=[4181, 177972])
visits_log[visits_log['end_ts'] < visits_log['start_ts']]
Создадим столбец с длительностью
visits_log['duration_ts'] = (visits_log['end_ts'] - visits_log['start_ts'])/ np.timedelta64(1,'s')
visits_log['duration_ts']
Подозрительные данные, в первых строках - видно, что сессии округлены до минут, и некоторые в меньшую сторону, поэтому равны 0
zero_duration_uids = visits_log[visits_log['duration_ts'] == 0]['uid']
orders_log.query('(uid in @zero_duration_uids) and (revenue > 0)')['uid'].count()
При том, что у людей нулевые сессии, 10985 из них совершали покупки... вот почему не стоит просто брать и удалять нулевые сессии
visits_log['duration_ts'].plot(title='Длительности всех сессий', grid=True, figsize=(16, 8), style='o', alpha=0.1)
plt.ylabel('Длительность сессии')
plt.xlabel('Номер сессии')
plt.show()
visits_log['duration_ts'].describe()
Средняя продолжительность сессии 10 минут 43 секунды, однако медиана прошла в 5 минутах, т.е. выбросы некоторых сессий отклонили среднюю больше чем в 2 раза от медианы. Что будет если удалить выбросы, например ограничимся 30 минутами? (1800 секунд, такое значение взято т.к. это стандарт в Яндекс.Метрике)
true_visits_log = visits_log[visits_log['duration_ts'] <= 1800]['duration_ts']
true_visits_log.plot.box(grid=True, figsize=(6, 8))
plt.ylabel('Длительность сессии')
plt.show()
true_visits_log.describe()
Вот теперь медиана осталась на прежнем уровне, а средняя уменьшилась сразу на 3,5 минуты (10:43 -> 7:11), при этом количество записей сократилось всего на 7,8%
Итак, предлагаю считать 7 минут 11 секунд средней длительностью большинства посещений¶
Посмотрим как менялась продолжительность сессии в течение года
(visits_log
.query('duration_ts in @true_visits_log')
.groupby('week')['duration_ts']
.mean()
.plot.line(title="График изменения средней продолжительности сессии по дням", grid=True, figsize=(16, 8))
)
plt.ylabel('Средняя длительность сессии')
plt.xlabel('Время')
plt.show()
В целом на протяжении года продолжительность сессии не сильно менялась, от 430 +/- 30 секунд. Летом меньше, зимой больше.
(visits_log
.query('(duration_ts in @true_visits_log) and (source_id != 6) and (source_id != 7)') # у 6,7 источника мало данных, график не отражает реальность
.groupby(['source_id','week'])['duration_ts']
.mean()
.reset_index()
.pivot_table(index='week', columns='source_id', values='duration_ts', aggfunc='mean')
.plot(title='Средняя длительность сессии по источникам', grid=True)
)
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 9','Источник 10'])
plt.ylabel('Средняя длительность сессий')
plt.xlabel('Время')
plt.show()
Лидерами по продолжительности сессии стали 1 и 2 источники, видимо по ним приходят ещё не знакомые с сайтом, поэтому они долго изучают и выбирают. Так же можно предположить, что это и более лояльные люди, готовые покупать.
(visits_log
.query('duration_ts in @true_visits_log')
.groupby(['device','week'])['duration_ts']
.mean()
.reset_index()
.pivot_table(index='week', columns='device', values='duration_ts', aggfunc='mean')
.plot(title='Средняя длительность сессии по устройствам', grid=True, color=['darkgreen','brown'])
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('Средняя длительность сессий')
plt.xlabel('Время')
plt.show()
Трафик с мобильных значительно быстрее уходит сайта: либо сайт на мобильном даёт нужную информацию доступнее и быстрее, либо наоборот, изучать сайт в таком виде не удобно и люди переходят в ПК. Я склоняюсь ко второму.
source_df = (visits_log
.query('(duration_ts in @true_visits_log) and (source_id != 6) and (source_id != 7)')
.groupby('source_id')
.agg({'duration_ts':'mean'})
.reset_index()
)
source_and_device_df = (visits_log
.query('(duration_ts in @true_visits_log) and (source_id != 6) and (source_id != 7)')
.groupby(['source_id','device'])
.agg({'duration_ts':'mean'})
.reset_index()
.pivot_table(index='source_id', columns='device', values='duration_ts', aggfunc='mean')
)
ax = sns.barplot(x='source_id', y='duration_ts', palette="ch:.5", data=source_df)
source_and_device_df.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
plt.title('Средняя длительность сессий по источникам и устройствам')
plt.ylabel('Средняя длительность сессий')
plt.xlabel('Источники')
plt.show()
1 источник даёт самый усидчевый трафик, а в целом средняя продолжительность сессии везде одинакова, и относительная разница ПК с мобильным трафиком так же везде одинакова.
Как часто люди возвращаются?
Создадим таблицу с первыми посещениями пользователей и соединим с основной
first_activity_date = visits_log.groupby(['uid'])['start_ts'].min()
first_activity_date.name = 'first_session'
visits_log = visits_log.join(first_activity_date,on='uid')
visits_log['first_session_month'] = visits_log['first_session'].astype('datetime64[M]')
visits_log.head()
Сгруппируем таблицу для создания когорт
visits_by_cohorts = visits_log.groupby(['first_session_month','month']).agg({'start_ts':'count','uid':'nunique'}).reset_index()
visits_by_cohorts = visits_by_cohorts.rename(columns = {'start_ts':'count_of_sessions', 'uid':'unique_users'})
visits_by_cohorts.head()
visits_by_cohorts['cohort_month'] = (( visits_by_cohorts['month'] - visits_by_cohorts['first_session_month'] ) / np.timedelta64(1, 'M')).round().astype('int') # месяц/когорта
visits_by_cohorts['first_session_month'] = visits_by_cohorts['first_session_month'].dt.strftime('%Y-%m')
Сделаем временную таблицу с количеством уникальных пользователей когорт
initial_users = visits_by_cohorts[visits_by_cohorts['cohort_month'] == 0][['first_session_month','unique_users']]
initial_users = initial_users.rename(columns = {'unique_users':'cohort_users'})
initial_users
visits_by_cohorts = visits_by_cohorts.merge(initial_users, on='first_session_month')
visits_by_cohorts['retention'] = visits_by_cohorts['unique_users']/visits_by_cohorts['cohort_users'] # столбец с процентом возврата пользователей
retention_pivot = visits_by_cohorts.pivot_table(index='first_session_month',columns='cohort_month',values='retention',aggfunc='sum') # pivot для построения тепловой карты когорт
plt.figure(figsize=(16, 9))
plt.title('Retention rate')
sns.heatmap(retention_pivot, vmax=0.1, annot=True, fmt='.1%', linewidths=1, linecolor='gray')
plt.ylabel('Retention rate (удержание)')
plt.xlabel('Возраст когорты (месяц)')
plt.show()
Видно как хорошо возвращаются люди из первой когорты, и не очень хорошо возвращаются люди из последующих когорт. Но нужно понимать, что в первую когорту входят все те, кто ещё раньше (за пределами статистики) стали постоянными посетителями. Т.е. если бы мы взяли когорты за прошлые года, то когорта 2017-06 выглядела бы совершенно иначе и была больше похожа на все последующие (2017-07, 2017-08 и т.д.)
А вот когорту 2017-09 действительно можно рассматривать как особо успешную, там в следующем месяце вернулось 8,5% людей против 5-6% в других когортах. И в следующем месяце тоже лучший показатель возврата. Стоит посмотреть, что было сделано для того, чтобы вернуть людей в те месяцы и ввести данные меры в регулярную деятельность
(visits_by_cohorts[visits_by_cohorts['cohort_month'] > 0]
.groupby('first_session_month')['retention']
.mean()
.plot.line(title='График изменения среднего значения retention rate (% возврата пользователей) по месяцам', color='red', grid=True, figsize=(16,6))
)
plt.ylabel('Средний retention rate (удержание)')
plt.xlabel('Время')
plt.show()
(visits_by_cohorts[visits_by_cohorts['cohort_month'] > 0]
.groupby('cohort_month')['retention']
.mean()
.plot.line(title='График изменения среднего значения retention rate по когортам', color='darkorange', grid=True, figsize=(16,6))
)
plt.ylabel('Средний retention rate (удержание)')
plt.xlabel('Возраст когорты (месяц)')
plt.show()
Видно, что и с течением времени когорта меньше посещает сайт (в среднем), и у новых когорт падает процент возврата. Получается новым пользователям сайт кажется не таким полезным, чтобы на него возвращаться, я бы предложил создать новостную рассылку и с умеренной агресивностью предлагать подписаться новым пользователям, напремер еженедельник о новых мероприятиях, а письма ссылались бы на сайт, тем самым возвращали людей обратно, за покупками.
Churn Rate
Chern Rate – это процент посетителей, которые отказались от повторного посещения сайта на следующий период (месяц, например) или вернувшиеся, если процент положительный, т.е. их стало больше, чем в прошлом периоде.
visits_by_cohorts['churn_rate'] = visits_by_cohorts.groupby(['first_session_month'])['unique_users'].pct_change() #процент изменения по отношению к предыдущему периоду
churn_pivot = visits_by_cohorts.pivot_table(index='first_session_month',columns='cohort_month',values='churn_rate',aggfunc='sum')
sns.set(style='white')
plt.figure(figsize=(16, 8.9))
sns.heatmap(churn_pivot, annot=True, fmt='.1%', linewidths=1, linecolor='gray');
plt.title('Churn Rate')
plt.ylabel('Когорта, churn rate')
plt.xlabel('Возраст когорты (месяцев)')
plt.show()
Видно, что возвращаются в следующий месяц после первого очень мало, 6-8%. дальше падение трафика продолжается, но не всегда. Есть месяцы когда трафик увеличивался по отношению к предыдущему месяцу, видимо проводились какие-то меры для возврата пользователей.
Здесь и далее будем вычислять показатели по 6 месяцам, т.к. нужно не забывать, что в 1 когорте 12 исследуемых месяцев, а в последней только 1, поэтому для честного исследования среднего показателя retention rate за год, нужна статистика за 2 года, откуда будет взят полностью заполненный квадрат 12х12 когорт/месяцев. Зато у нас есть полноценные 6 когорт с 7 месяцами возраста, в квадрате 6х7
retention_by_source = (visits_log
.query('first_session_month <= "2017-11-01"') # 6 месяцев
.groupby(['first_session_month','month','source_id'])
.agg({'uid':'nunique'})
.reset_index()
)
retention_by_source['cohort_month'] = (( retention_by_source['month'] - retention_by_source['first_session_month'] ) / np.timedelta64(1, 'M')).round().astype('int') # месяц/когорта
initial_users = retention_by_source[retention_by_source['cohort_month'] == 0][['first_session_month','source_id','uid']] # таблица с исходными юзерами (0 когорта)
initial_users = initial_users.rename(columns = {'uid':'cohort_users'}) # переименование
retention_by_source = retention_by_source.merge(initial_users, on=['first_session_month', 'source_id']) # соединение таблиц с исходными юзерами
retention_by_source['retention'] = retention_by_source['uid']/retention_by_source['cohort_users'] # Retention rete
(retention_by_source
.query('(cohort_month != 0) and (cohort_month <= 6) and (source_id != 6) and (source_id != 7)') # 1-6 возраста когорт, 0-возраст исключен для наглядности графика + исключаем источники 6,7
.pivot_table(index='cohort_month', columns='source_id', values='retention', aggfunc='mean')
.plot(title='Средний retention rate по источникам', grid=True)
)
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 9','Источник 10'])
plt.ylabel('Средний retention rate (удержание)')
plt.xlabel('Возраст когорты')
plt.show()
Средняя возвращаемость 1, 2 и, внезапно, 9 источника наиболее высокая, но у 9-го идёт падение с каждой когортой.
retention_by_device = (visits_log
.query('first_session_month <= "2017-11-01"') # 6 месяцев
.groupby(['first_session_month','month','device'])
.agg({'uid':'nunique'})
.reset_index()
)
retention_by_device['cohort_month'] = (( retention_by_device['month'] - retention_by_device['first_session_month'] ) / np.timedelta64(1, 'M')).round().astype('int') # месяц/когорта
initial_users = retention_by_device[retention_by_device['cohort_month'] == 0][['first_session_month','device','uid']]
initial_users = initial_users.rename(columns = {'uid':'cohort_users'})
retention_by_device = retention_by_device.merge(initial_users, on=['first_session_month', 'device'])
retention_by_device['retention'] = retention_by_device['uid']/retention_by_device['cohort_users']
(retention_by_device
.query('(cohort_month != 0) and (cohort_month <= 6)') # 1-6 возраста когорт, 0-возраст исключен для наглядности графика
.pivot_table(index='cohort_month', columns='device', values='retention', aggfunc='mean')
.plot(title='Средний retention rate по устройствам за 6 месяцев', grid=True, color=['darkgreen','brown'])
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('Средний retention rate (удержание)')
plt.xlabel('Время')
plt.show()
В среднем по каждой когорте возвращаемость падает. Можно увидеть, что люди с ПК в среднем возвращаются больше.
retention_by_source = (visits_log
.query('first_session_month <= "2017-11-01"') # 6 месяцев
.groupby(['first_session_month','month','source_id'])
.agg({'uid':'nunique'})
.reset_index()
)
retention_by_source['cohort_month'] = (( retention_by_source['month'] - retention_by_source['first_session_month'] ) / np.timedelta64(1, 'M')).round().astype('int') # месяц/когорта
initial_users = retention_by_source[retention_by_source['cohort_month'] == 0][['first_session_month','source_id','uid']]
initial_users = initial_users.rename(columns = {'uid':'cohort_users'})
retention_by_source = retention_by_source.merge(initial_users, on=['first_session_month','source_id'])
retention_by_source['retention'] = retention_by_source['uid']/retention_by_source['cohort_users']
retention_by_source_and_device = (visits_log
.query('first_session_month <= "2017-11-01"') # 6 месяцев
.groupby(['first_session_month','month','device','source_id'])
.agg({'uid':'nunique'})
.reset_index()
)
retention_by_source_and_device['cohort_month'] = (( retention_by_source_and_device['month'] - retention_by_source_and_device['first_session_month'] ) / np.timedelta64(1, 'M')).round().astype('int') # месяц/когорта
initial_users = retention_by_source_and_device[retention_by_source_and_device['cohort_month'] == 0][['first_session_month','device','source_id','uid']]
initial_users = initial_users.rename(columns = {'uid':'cohort_users'})
retention_by_source_and_device = retention_by_source_and_device.merge(initial_users, on=['first_session_month', 'device','source_id'])
retention_by_source_and_device['retention'] = retention_by_source_and_device['uid']/retention_by_source_and_device['cohort_users']
source_df = (retention_by_source
.query('(cohort_month != 0) and (cohort_month <= 6) and (source_id != 6) and (source_id != 7)') # 1-6 возраста когорт + исключаем источники 6,7
.groupby('source_id')
.agg({'retention':'mean'})
.reset_index()
)
source_and_device_df = (retention_by_source_and_device
.query('(cohort_month != 0) and (cohort_month <= 6) and (source_id != 6) and (source_id != 7)') # 1-6 возраста когорт + исключаем источники 6,7
.groupby(['source_id','device'])
.agg({'retention':'mean'})
.reset_index()
.pivot_table(index='source_id', columns='device', values='retention', aggfunc='mean')
)
ax = sns.barplot(x='source_id', y='retention', palette="ch:.7", data=source_df)
source_and_device_df.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
plt.title('Средний retention rate по источникам и устройствам')
plt.ylabel('Средний retention rate (удержание)')
plt.xlabel('Источники')
plt.show()
Подведем итог: 1,2,9 источники приводят трафик, который возвращается чаще. Особенно в 1 и 2 источнике выделяется ПК-трафик.
mean_retention = visits_by_cohorts.query('(cohort_month > 0)')
print(f'Общий средний Retention rate за всё время:{(mean_retention["retention"].mean()):.2%}')
Иногда проще держать в голове 1 цифру и в будущем сталкивась с показателями выше или ниже - понимать насколько всё хорошо или плохо. Например Retention rate - 4,5%
Когда люди начинают покупать?
Здесь можно рассмотреть "когда?" имея ввиду "через какое время после первой сессии?" и "когда?" в смысле "через сколько сессий?"
Я рассматрю оба случая.
Итак, через какое время после первой сессии совершают первую покупку?¶
orders_log['buy_day'] = orders_log['buy_ts'].dt.floor('1d')
first_buy_date = orders_log.groupby(['uid'])['buy_ts'].min() # выявляем даты первой покупки
first_buy_date.name = 'first_buy_date'
orders_log = orders_log.join(first_buy_date,on='uid') # соединяем с основной таблицей
orders_log.head(3)
visits_and_orders = visits_log.merge(orders_log, on='uid') # соединяем с таблицей с визитами
visits_and_orders['duration_to_buy'] = (
((visits_and_orders['first_buy_date'] - visits_and_orders['first_session']) / np.timedelta64(1, 's'))
.round()
.astype('int')
) # добавили столбец с длительностью от первой сессии до первой продажи в секундах
visits_and_orders.head(3)
print(f"Среднее время до покупки за исследуемое время (1 год) составляет {int(round(visits_and_orders['duration_to_buy'].mean()/60/60/24))} дней")
Целых 18 дней, НО! Это всё равно, что считать среднюю температуру по больнице. Например посмотрим на медиану:
print(f"Медианное время до покупки за исследуемое время составляет {int(round(visits_and_orders['duration_to_buy'].median()/60/60/24))} дня")
Уже 2 дня! Т.е. половина людей совершивших покупку, совершили её посетив сайт не более 2 дней назад. Ну и соответственно во второй половине людей есть те, кто покупал первый раз через несколько месяцев и даже через год:
print(f"Максимальное время до покупки за исследуемое время составляет {int(round(visits_and_orders['duration_to_buy'].max()/60/60/24))} дня")
Теперь посмотрим как это всё выглядит на графике
visits_and_orders['duration_to_buy_hours'] = (visits_and_orders['duration_to_buy']/60/60).round(2)
(visits_and_orders
.sort_values('duration_to_buy_hours')
.pivot_table(index='first_session_month', values='duration_to_buy_hours', aggfunc=['min','median','mean','max'])
.plot(style='o-', grid=True, figsize=(16,8))
)
plt.legend(['Минимальное время до покупки','Медианное время до покупки','Среднее время до покупки','Максимальное время до покупки'])
plt.ylabel('Часы до первой покупки')
plt.xlabel('Время')
plt.show()
На этом графике явно видно, что разброс во времени может достигать максимально возможного из рассматриваемого периода, т.е. не важно когда человек первый раз посетил сайт, он может купить в любое время. Теперь рассмотрим медиану и среднее значение поближе:
(visits_and_orders
.sort_values('duration_to_buy_hours')
.pivot_table(index='first_session_month', values='duration_to_buy_hours', aggfunc=['min','median','mean'])
.plot(style='o-', grid=True, figsize=(16,8))
)
plt.legend(['Минимальное время до покупки','Медианное время до покупки','Среднее время до покупки'])
plt.ylabel('Часы до первой покупки')
plt.xlabel('Время')
plt.show()
Минимальное время от посещения до покупки всё так же на нуле, часть людей покупают сразу или не дают себя корректно отслеживать, таким образом время сеанса и покупки - одно и то же. Средняя и медиана первых месяцев поднимаются с течением времени, чем больше человек покупают всё позже и позже - тем сильнее увеличиваются эти показатели.
(visits_and_orders
.query('(source_id != 6) and (source_id != 7)')
.groupby(['source_id','month'])['duration_to_buy_hours']
.mean()
.reset_index()
.pivot_table(index='month', columns='source_id', values='duration_to_buy_hours', aggfunc='mean')
.plot(title='Среднее время (часы) до первой покупки по источникам', grid=True)
)
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 9','Источник 10'])
plt.ylabel('Среднее время (часы) до первой покупки')
plt.xlabel('Время')
plt.show()
Наиболее быстро покупают люди из 5-то источника, и это сохраняется на протяжении всего года. Так же быстро принимают решения люди из 1 и 2 источника, но в последние месяцы они начали долго думать, так же 3 источник подаёт надежды на быструю отдачу от вложений в него (время до покупки падает)
(visits_and_orders
.query('(source_id != 6) and (source_id != 7)')
.groupby(['device','month'])['duration_to_buy_hours']
.mean()
.reset_index()
.pivot_table(index='month', columns='device', values='duration_to_buy_hours', aggfunc='mean')
.plot(title='Среднее время (часы) до первой покупки по устройствам', grid=True, color=['darkgreen','brown'])
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('Среднее время (часы) до первой покупки')
plt.xlabel('Время')
plt.show()
На ПК люди покупают намного быстрее, и ситуация сохраняется на протяжении года. А на мобильных люди всё дольше принимают решения.
source_df = (visits_and_orders
.query('(source_id != 6) and (source_id != 7)')
.groupby('source_id')
.agg({'duration_to_buy_hours':'mean'})
.reset_index()
)
source_and_device_df = (visits_and_orders
.query('(source_id != 6) and (source_id != 7)')
.groupby(['source_id','device'])
.agg({'duration_to_buy_hours':'mean'})
.reset_index()
.pivot_table(index='source_id', columns='device', values='duration_to_buy_hours', aggfunc='mean')
)
ax = sns.barplot(x='source_id', y='duration_to_buy_hours', palette="ch:.9", data=source_df)
source_and_device_df.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
plt.title('Среднее время (часы) до первой покупки по источникам и устройствам')
plt.ylabel('Среднее время (часы) до первой покупки')
plt.xlabel('Источники')
plt.show()
1 и 5 источник дают наиболее быстропокупающих людей, но всю статистику портит мобильный трафик, возможно в рекламе стоит сократить расходы на мобильных пользователей (например в Яндекс.Директ есть такой инструмент как корректировка ставок на устройства). И нужно заняться доработкой мобильной версии сайта, запускать А/Б-тесты для поиска решения, когда люди будут покупать быстрее и чаще.
Теперь посмотрим на какой по счёту сеанс покупают¶
sessions_before_buy = (
visits_and_orders[visits_and_orders['start_ts'] <= visits_and_orders['first_buy_date']] #отсекли все сессии после первой покупки
.groupby(['uid', 'first_buy_date'])['start_ts'] # берем уников с их днями первой покупки и третьим столбцом пишем количество сессий до покупки включительно
.count()
.reset_index()
)
sessions_before_buy = sessions_before_buy.rename(columns = {'start_ts':'sessions_before_buy'})
sessions_before_buy.sort_values('first_buy_date').head(10)
color = {'boxes': 'DarkGreen', 'whiskers': 'DarkOrange', 'medians': 'DarkBlue', 'caps': 'Red'}
fig, axes = plt.subplots(1, 3, figsize=(16, 10))
(sessions_before_buy
.plot.box(x='first_buy_date', y='sessions_before_buy', title='Выбросы в количестве сессий до покупки', grid=True, ax=axes[0], color=color)
)
(sessions_before_buy
.query('sessions_before_buy < 200')
.plot.box(x='first_buy_date', y='sessions_before_buy', title='Количество сессий до покупки (до 200 сессий)', grid=True, ax=axes[1], color=color)
)
(sessions_before_buy
.query('sessions_before_buy < 10')
.plot.box(x='first_buy_date', y='sessions_before_buy', title='Количество сессий до покупки (до 10 сессий)', grid=True, ax=axes[2], color=color)
)
plt.show()
(sessions_before_buy
.sort_values('first_buy_date')
.query('sessions_before_buy <= 10')
.plot.hist(x='first_buy_date', y='sessions_before_buy', title='Количество сессий до покупки (до 10 сессий) и количество пользователей', grid=True, color='gold', figsize=(16,8))
)
plt.legend(['Количество сессий до покупки'])
plt.ylabel('Количество уникальных пользователей')
plt.xlabel('Количество сессий до покупки')
plt.show()
print(f"Количество людей купивших в первые 4 сессий: {sessions_before_buy[sessions_before_buy['sessions_before_buy'] <= 4]['uid'].count()}")
print(f"Количество людей купивших на 5-ю и более сессий: {sessions_before_buy[sessions_before_buy['sessions_before_buy'] > 4]['uid'].count()}")
Боксплоты показывают, что присутствуют сильные выбросы, но количество людей купивших с 1-й по 4-ю сессии - подавляющее большинство, и лишь ~6% совершают покупки позже.
Посмотрим, влияет ли время на то как быстро (за какое количество сессий) человек принимает решение купить
sessions_before_buy[sessions_before_buy['sessions_before_buy'] > 200]['sessions_before_buy'].count() # выбросы
Этих 24 пользователей я учитывать не буду, аномально много они думали перед покупкой (> 200)
sessions_before_buy_plot =(sessions_before_buy
.sort_values('first_buy_date')
.query('sessions_before_buy < 200')
#.plot.scatter(x='date_num', y='sessions_before_buy', title='Количество сессий до покупки (до 200 сессий) в разное время', grid=True, color='purple', figsize=(16,8))
)
plt.plot_date(sessions_before_buy_plot['first_buy_date'], sessions_before_buy_plot['sessions_before_buy'], color='purple')
plt.title('Количество сессий до первой покупки (до 200 сессий) во времени')
plt.ylabel('Количество сессий до покупки')
plt.xlabel('Время')
plt.show()
Как видно из графика, во все времена люди могут как сразу принимать решение о покупке, так и возвращаться десятки раз до принятия решения или поиска нужного билета. Но как мы выяснили ранее, почти 90% людей которые купили - сделали это в первые сеансы
sessions_before_buy_by_source = visits_log.merge(sessions_before_buy, on=['uid'] )
sessions_before_buy_by_source.head()
(sessions_before_buy_by_source
.query('(sessions_before_buy < 200) and (source_id != 6) and (source_id != 7)')
.groupby(['source_id','month'])['sessions_before_buy']
.mean()
.reset_index()
.pivot_table(index='month', columns='source_id', values='sessions_before_buy', aggfunc='mean')
.plot(title='Среднее количество сессий до первой покупки (до 200 сессий) по источникам', grid=True)
)
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 9','Источник 10'])
plt.ylabel('Среднее количество сессий до первой покупки')
plt.xlabel('Время')
plt.show()
Основной трафик не решительных и интересующихся приводят 1 и 2 источники. По остальным приходят люди, которые покупают на 5+/- 2 сеанса. Не забываем, что я ограничил здесь количество сессий до покупки до 200, т.е. если сделать срез на "до 10 сеансов" до покупки, ситуация изменится и окажется, что наиболее решительных людей дает 10 источник
(sessions_before_buy_by_source
.query('(sessions_before_buy < 200) and (source_id != 6) and (source_id != 7)')
.groupby(['device','month'])['sessions_before_buy']
.mean()
.reset_index()
.pivot_table(index='month', columns='device', values='sessions_before_buy', aggfunc='mean')
.plot(title='Среднее количество сессий до первой покупки (до 200 сессий) по устройствам', grid=True, color=['darkgreen','brown'])
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('Среднее количество сессий до первой покупки')
plt.xlabel('Время')
plt.show()
А вот здесь мобильные устройства вырывают пальму первенства у ПК. Как мы помним по времени пользователи мобильных принимают решение долго, но зато делают это чаще в первые сеансы. В зимние месяцы этот показатель приближается к ПК-версии и нужно отметить, что разница давольно большая между сентябрем (3 сессии до пукпки) и ноябрем (9 сессий до покупки), возможно ещё потому что в ноябре на черную пятницу набежало много людей обновляющих сайт в поисках халявы.
source_df = (sessions_before_buy_by_source
.query('(sessions_before_buy < 200) and (source_id != 6) and (source_id != 7)')
.groupby('source_id')
.agg({'sessions_before_buy':'mean'})
.reset_index()
)
source_and_device_df = (sessions_before_buy_by_source
.query('(sessions_before_buy < 200) and (source_id != 6) and (source_id != 7)')
.groupby(['source_id','device'])
.agg({'sessions_before_buy':'mean'})
.reset_index()
.pivot_table(index='source_id', columns='device', values='sessions_before_buy', aggfunc='mean')
)
ax = sns.barplot(x='source_id', y='sessions_before_buy', palette="ch:1.0", data=source_df)
source_and_device_df.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
plt.title('Среднее количество сессий до первой покупки по источникам и устройствам')
plt.ylabel('Среднее количество сессий до первой покупки')
plt.xlabel('Источники')
plt.show()
Итого: 3,4,9,10 источники дают наиболее быстрых (с точки зрения количества сессий) покупателей. Статистику 1,2 и 5 источников портят ПК-покупатели, коготые очень много раз возвращаются перед первой покупкой. Возможно на ПК приходят люди которые ищут "куда бы сходить", а на мобильную версию приходят те, кто уже знают куда идти и покупают быстрее.
Сколько раз покупают за период?
Тут нам понадобятся месяцы у каждой покупки в orders_log, чтобы поделить по когортам
orders_log['first_buy_month'] = orders_log['first_buy_date'].astype('datetime64[M]')
orders_log['buy_month'] = orders_log['buy_ts'].astype('datetime64[M]')
orders_log.head(3)
Кроме нужных показателей: числа уникальных покупателей, и числа покупок, добавлю в таблицу сумму дохода для будущих исследований
orders_by_cohorts = orders_log.groupby(['first_buy_month', 'buy_month']).agg({'revenue':'sum','buy_ts':'count','uid':'nunique'}).reset_index()
orders_by_cohorts['cohort_month'] = (( orders_by_cohorts['buy_month'] - orders_by_cohorts['first_buy_month'] ) / np.timedelta64(1, 'M')).round().astype('int') # месяц/когорта
orders_by_cohorts = orders_by_cohorts.rename(columns = {'revenue':'revenue_sum','buy_ts':'buy_count','uid':'uid_count'})
orders_by_cohorts.head()
orders_by_cohorts['first_buy_month'] = orders_by_cohorts['first_buy_month'].dt.strftime('%Y-%m')
orders_per_user_pivot = (orders_by_cohorts
.query('first_buy_month < "2018-06"') # 2018-06 - этот месяц не полный, там всего 1 покупка
.pivot_table(index='first_buy_month',columns='cohort_month',values='buy_count',aggfunc='mean')
)
plt.figure(figsize=(16, 8.9))
sns.heatmap(orders_per_user_pivot, vmax=500, fmt='.0f',annot=True, linewidths=1, linecolor='gray')
plt.title('Количество покупок пользователей по когортам')
plt.ylabel('Количество покупок')
plt.xlabel('Возраст когорты (месяцы)')
plt.show()
Много людей из июньской когорты совершают повторные покупки и через несколько месяцев, но в целом их было мало - 2354, для сравнения в декабре когорта составила 5052 покупок от уникальных пользователей. Ещё интересно, что люди пришедшие в ноябре (когда была черная пятница) вернулись за покупками и в декабре, более охотно чем другие когорты на следующий месяц.
((visits_and_orders
.groupby(['month', 'source_id'])
.agg({'buy_ts':'count'})
.reset_index()
.rename(columns = {'buy_ts':'buy_count'})
)
.query('(source_id != 6) and (source_id != 7)') # у 6,7 источника мало данных, график не отражает реальность
.groupby(['month', 'source_id'])['buy_count']
.mean()
.reset_index()
.pivot_table(index='month', columns='source_id', values='buy_count', aggfunc='mean')
.plot(title='Среднее количество покупок пользователей по источникам', grid=True)
)
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 9','Источник 10'])
plt.ylabel('Среднее количество покупок')
plt.xlabel('Время')
plt.show()
1 и 2 источники в среднем приводят больше покупок (повторных в том числе)
((visits_and_orders
.groupby(['month', 'device'])
.agg({'buy_ts':'count'})
.reset_index()
.rename(columns = {'buy_ts':'buy_count'})
)
.groupby(['month', 'device'])['buy_count']
.mean()
.reset_index()
.pivot_table(index='month', columns='device', values='buy_count', aggfunc='mean')
.plot(title='Среднее количество покупок пользователей по устройствам', grid=True, color=['darkgreen','brown'])
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('Среднее количество покупок')
plt.xlabel('Время')
plt.show()
ПК - лучший способ выбирать покупку, здесь нет сомнений, что даже те, кто изначально покупал на мобильном, потом перешли на ПК.
source_df = ((visits_and_orders
.groupby('source_id')
.agg({'buy_ts':'count'})
.reset_index()
.rename(columns = {'buy_ts':'buy_count'})
)
.query('(source_id != 6) and (source_id != 7)')
.groupby('source_id')
.agg({'buy_count':'mean'})
.reset_index()
)
source_and_device_df = ((visits_and_orders.groupby(['source_id','device'])
.agg({'buy_ts':'count'})
.reset_index()
.rename(columns = {'buy_ts':'buy_count'})
)
.query('(source_id != 6) and (source_id != 7)')
.groupby(['source_id','device'])
.agg({'buy_count':'mean'})
.reset_index()
)
plt.title('Среднее количество покупок пользователей по источникам и устройствам')
ax = sns.barplot(x='source_id', y='buy_count', palette="ch:1.3", data=source_df)
(source_and_device_df
.pivot_table(index='source_id', columns='device', values='buy_count', aggfunc='mean')
.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
)
plt.ylabel('Среднее количество покупок')
plt.xlabel('Источники')
plt.show()
1,2,5 источники лидируют по количеству покупок. Здесь видно на сколько ПК сильно лидирует по отношению к мобильным устройствам. А 9 и 10 источники приводят очень мало покупок, возможно это не популярные каналы.
orders_per_user_mean = (orders_by_cohorts
.query('(cohort_month <= 6) and (first_buy_month <= "2017-11")')
.groupby('first_buy_month')
.agg({'uid_count':'max','buy_count':'sum'})
.reset_index()
)
orders_per_user_mean['orders_per_user'] = (orders_per_user_mean['buy_count'] / orders_per_user_mean['uid_count']).round(2)
print()
print('Среднее количество покупок на пользователя по когортам за 6 месяцев')
orders_per_user_mean.head()
print(f"Среднее число покупок на человека за 6 месяцев: {(orders_per_user_mean['buy_count'].sum() / orders_per_user_mean['uid_count'].sum()).round(2)}")
Т.е. вероятнее всего 1 человек совершит 1 покупку и лишь в когорте за июнь 2017 люди были особо активными и покупали чаще, почти 2 покупки совершал каждый человек в среднем за 6 месяцев.
Для наглядности покажу ещё одну таблицу, которая расставит все точки над i :
orders_mean = (orders_log
.groupby(['first_buy_month','buy_month','uid'])
.agg({'buy_ts':'count'})
.reset_index()
)
orders_mean['cohort_month'] = (( orders_mean['buy_month'] - orders_mean['first_buy_month'] ) / np.timedelta64(1, 'M')).round().astype('int') # месяц/когорта
orders_mean = orders_mean.rename(columns = {'buy_ts':'buy_count'})
orders_mean = orders_mean.query('(cohort_month <= 6) and (first_buy_month <= "2017-11")')
orders_mean = orders_mean.groupby(['uid']).agg({'buy_count':'sum'}).sort_values('buy_count', ascending=False).reset_index()
orders_mean.groupby(['buy_count']).count().reset_index().plot(x='buy_count', y='uid', kind='bar', color='g')
plt.title('Сколько покупок у скольких пользователей за 6 месяцев')
plt.ylabel('Количество пользователей')
plt.xlabel('Количество покупок')
plt.show()
Дополнительный график, показывающий сколько покупок у скольких пользователей. Здесь видно, что большинство покупало 1 раз, а 6 и более покупок совершают еденицы
Какой средний чек?
orders_by_cohorts['average_bill'] = orders_by_cohorts['revenue_sum'] / orders_by_cohorts['buy_count'] # средний чек
orders_by_cohorts.tail()
average_bill_pivot = (orders_by_cohorts
.query('first_buy_month < "2018-06"') # 2018-06 - этот месяц не полный, там всего 1 покупка
.pivot_table(index='first_buy_month',columns='cohort_month',values='average_bill',aggfunc='mean')
)
plt.figure(figsize=(16, 8.9))
sns.heatmap(average_bill_pivot, vmax=20, annot=True, fmt='.1f', linewidths=1, linecolor='gray')
plt.title('Средний чек покупателей по когортам')
plt.ylabel('Средний чек')
plt.xlabel('Возраст когорты (месяц)')
plt.show()
Когорты сентября и декабря очень удачные, их средние чеки наерняка отбивают затраты по ним уже через полгода, это мы ещё увидим. Особенно выделяется сентябрьская когорта в декабре, в 10 раз превышая показатели почти всех других когорт.
visits_and_orders['first_buy_month'] = visits_and_orders['first_buy_date'].astype('datetime64[M]')
visits_and_orders['buy_month'] = visits_and_orders['buy_ts'].astype('datetime64[M]')
orders_by_cohorts_and_source = visits_and_orders.groupby(['first_buy_month', 'buy_month', 'source_id']).agg({'revenue':'sum','buy_ts':'count','uid':'nunique'}).reset_index()
orders_by_cohorts_and_source['cohort_month'] = (( orders_by_cohorts_and_source['buy_month'] - orders_by_cohorts_and_source['first_buy_month'] ) / np.timedelta64(1, 'M')).round().astype('int') # месяц/когорта
orders_by_cohorts_and_source = orders_by_cohorts_and_source.rename(columns = {'buy_ts':'buy_count','uid':'uid_count'})
orders_by_cohorts_and_source['average_bill'] = orders_by_cohorts_and_source['revenue'] / orders_by_cohorts_and_source['buy_count'] # средний чек
orders_by_cohorts_and_source.head()
orders_by_source = (visits_and_orders
.groupby(['month', 'source_id'])
.agg({'revenue':'sum','buy_ts':'count'})
.reset_index()
)
orders_by_source['average_bill'] = orders_by_source['revenue'] / orders_by_source['buy_ts']
(orders_by_source
.query('(source_id != 6) and (source_id != 7)')
.groupby(['month', 'source_id'])
.agg({'average_bill':'max'}) # декоративная функция - ничего не делает (можно вписать любую), но позваляет создть график
.reset_index()
.pivot_table(index='month', columns='source_id', values='average_bill', aggfunc='max')
.plot(title='Средний чек по источникам', grid=True)
)
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 9','Источник 10'])
plt.ylabel('Средний чек')
plt.xlabel('Время')
plt.show()
Наиболее хорошие средние чеки у 1 и 2 источника. 5 источник привлёк в сентябре богатых покупателей, которые на протяжении всего года выделялись большими чеками, согласно когортному анализу. Стоит побольше поработать с этим источником и повысить бюджет на него.
orders_by_device = (visits_and_orders
.groupby(['week', 'device'])
.agg({'revenue':'sum','buy_ts':'count'})
.reset_index()
)
orders_by_device['average_bill'] = orders_by_device['revenue'] / orders_by_device['buy_ts']
(orders_by_device
.groupby(['week', 'device'])
.agg({'average_bill':'max'}) # декоративная функция - ничего не делает (можно вписать любую), но позваляет создть график
.reset_index()
.pivot_table(index='week', columns='device', values='average_bill', aggfunc='max')
.plot(title='Средний чек по устройствам', grid=True, color=['darkgreen','brown'])
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('Средний чек')
plt.xlabel('Время')
plt.show()
Десктопный трафик приводит более менее стабильные средние чеки, а мобильный сильно "прыгает", но в целом средний чек оттуда растёт.
orders_by_source = (visits_and_orders
.groupby('source_id')
.agg({'revenue':'sum','buy_ts':'count'})
.reset_index()
)
orders_by_source['average_bill'] = orders_by_source['revenue'] / orders_by_source['buy_ts']
orders_by_source_and_device = (visits_and_orders
.groupby(['source_id','device'])
.agg({'revenue':'sum','buy_ts':'count'})
.reset_index()
)
orders_by_source_and_device['average_bill'] = orders_by_source_and_device['revenue'] / orders_by_source_and_device['buy_ts']
source_df = (orders_by_source
.query('(source_id != 6) and (source_id != 7)')
.groupby('source_id') #[['source_id','average_bill']]
.agg({'average_bill':'max'}) # декоративная функция - ничего не делает (можно вписать любую), но позваляет создть график
.reset_index()
)
source_and_device_df = (orders_by_source_and_device
.query('(source_id != 6) and (source_id != 7)')
.groupby(['source_id','device']) #[['source_id','device','average_bill']]
.agg({'average_bill':'max'}) # декоративная функция - ничего не делает (можно вписать любую), но позваляет создть график
.reset_index()
)
plt.title('Средний чек по источникам и устройствам')
ax = sns.barplot(x='source_id', y='average_bill', palette="ch:1.5", data=source_df)
(source_and_device_df
.pivot_table(index='source_id', columns='device', values='average_bill')
.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
)
plt.ylabel('Средний чек')
plt.xlabel('Источники')
plt.show()
Здесь видно как средний чек по 1 источнику намного выше у мобильных устройств. И вообще - это первый график на котором мобильный и ПК трафик различается от источника к источнику. Наиболее хорошим можно назвать 2 источник - оттуда идут хорошие и ПК и мобильные средние чеки.
print(f"Ну а средний чек за всё время = {round((orders_by_cohorts['average_bill'].mean()),2)}")
orders_by_source.groupby('source_id')['average_bill'].mean()
orders_by_cohorts.groupby('first_buy_month')['average_bill'].mean()
Сколько денег приносят? (LTV)
Для того, чтобы узгать LTV в датасете не хватает только столбца с валовой прибылью. Т.к. нам не известна доходность сайта (маржа), предлагаю взять максимально близкое к правде число 50%. Почему 50% максимально близкое? Потому что 100% - это безубыточный бизнес, что не возможно, а за 0% прибыли никто бы не стал содержать бизнес столько лет, соответственно при 50% предпологаемой маржинальности, можно ошибиться максимум на 49,9%
margin_rate = 0.5
orders_by_cohorts['profit'] = orders_by_cohorts['revenue_sum'] * margin_rate
orders_by_cohorts['ltv'] = orders_by_cohorts['profit'] / orders_by_cohorts['uid_count']
orders_by_cohorts.head()
print(f"Средний LTV за всё исследуемое время: {round(orders_by_cohorts['ltv'].mean(), 2)} у.е.")
ltv_by_cohorts = (orders_by_cohorts
.query('first_buy_month < "2018-06"') # 2018-06 - этот месяц не полный, там всего 1 покупка
.pivot_table(index='first_buy_month', columns='cohort_month', values='ltv', aggfunc='mean')
.cumsum(axis=1)
.round(2)
)
plt.figure(figsize=(16, 8.9))
plt.title('LTV покупателей по когортам (кумулятивно)')
sns.heatmap(ltv_by_cohorts, annot=True, fmt='.2f', linewidths=1, linecolor='gray')
plt.ylabel('LTV, у.е.')
plt.xlabel('Возраст когорты (месяц)')
plt.show()
Видно как у каждой 3 когорты, на 3 месяц возникает желание потратить больше денег, возможно это дает плоды маркетинговая кампания (о которой я не знаю) или это ещё связано с премьерами, вполне возможно, что они запускаются каждый третий месяц.
Ситуация повторяется и на 6 месяц, но менее ярко. А вот когорты октября и ноября дают низкий LTV, стоит придумать для них рекламную кампанию, ведь покупателей, как мы помним из предыдущих отчётов, там много.
(orders_by_cohorts
.query('(cohort_month <= 6) and (first_buy_month <= "2017-11")')
.groupby('cohort_month')
.agg({'ltv':'mean'})
.reset_index()
.plot.bar(x='cohort_month', y='ltv', title='Средний LTV по когортам', grid=True, figsize=(11,8))
)
plt.legend(['LTV'])
plt.ylabel('Средний LTV, у.е.')
plt.xlabel('Возраст когорты (в месяцах)')
plt.show()
(orders_by_cohorts
.query('(cohort_month <= 6) and (first_buy_month <= "2017-11")')
.groupby('first_buy_month')
.agg({'ltv':'mean'})
.sort_values(by='first_buy_month', ascending=False)
.reset_index()
.plot.barh(x='first_buy_month', y='ltv', title='Средний LTV по 6 месяцам', grid=True, figsize=(8,8), color='lightblue')
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('Месяц (когорта)')
plt.xlabel('Средний LTV, у.е.')
plt.show()
С помоью этих графиков можно увидеть на сколько определенные месяцы и когорты дают больше отдачи (ltv) по отношению к другим. Сразу видно яркий 3 месяц возрата когорты и ключевые когорты: июнь, сентябрь.
ltv_by_source = (visits_and_orders
.groupby(['month','source_id'])
.agg({'revenue':'sum','uid':'nunique'})
.reset_index()
)
ltv_by_source['profit'] = ltv_by_source['revenue'] * margin_rate
ltv_by_source['ltv'] = ltv_by_source['profit'] / ltv_by_source['uid']
(ltv_by_source
.query('(source_id != 6) and (source_id != 7)')
.groupby(['source_id','month'])['ltv']
.mean()
.reset_index()
.pivot_table(index='month', columns='source_id', values='ltv', aggfunc='mean')
.plot(title='LTV по источникам', grid=True)
)
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 9','Источник 10'])
plt.ylabel('LTV, у.е.')
plt.xlabel('Время')
plt.show()
1 и 2 источники дают покупателей с наибольшим LTV. У 5 источника был всплеск в сентябре. Возможно по нему активно рекламировалась премьера нового события, да и потом сентябрьская когорта показывала хороший LTV.
ltv_by_device = (visits_and_orders
.groupby(['week','device'])
.agg({'revenue':'sum','uid':'nunique'})
.reset_index()
)
ltv_by_device['profit'] = ltv_by_device['revenue'] * margin_rate
ltv_by_device['ltv'] = ltv_by_device['profit'] / ltv_by_device['uid']
(ltv_by_device
.groupby(['week','device'])['ltv']
.mean()
.reset_index()
.pivot_table(index='week', columns='device', values='ltv', aggfunc='mean')
.plot(title='LTV по устройствам', grid=True, color=['darkgreen','brown'])
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('LTV, у.е.')
plt.xlabel('Время')
plt.show()
Дестктоп опять лидирует, LTV по нему выше во все времена. Мобильный LTV лишь в ноябре приблизились к ПК, ито благодяря просадке у ПК.
ltv_by_source_and_device = (visits_and_orders
.groupby(['source_id','device'])
.agg({'revenue':'sum','uid':'nunique'})
.reset_index()
)
ltv_by_source_and_device['profit'] = ltv_by_source_and_device['revenue'] * margin_rate
ltv_by_source_and_device['ltv'] = ltv_by_source_and_device['profit'] / ltv_by_source_and_device['uid']
ltv_by_source = (visits_and_orders
.groupby('source_id')
.agg({'revenue':'sum','uid':'nunique'})
.reset_index()
)
ltv_by_source['profit'] = ltv_by_source['revenue'] * margin_rate
ltv_by_source['ltv'] = ltv_by_source['profit'] / ltv_by_source['uid']
source_df = (ltv_by_source
.query('(source_id != 6) and (source_id != 7)')
.groupby('source_id')
.agg({'ltv':'max'}) # декоративная ф-я
.reset_index()
)
source_and_device_df = (ltv_by_source_and_device
.query('(source_id != 6) and (source_id != 7)')
.groupby(['source_id','device'])
.agg({'ltv':'max'}) # декоративная ф-я
.reset_index()
)
plt.title('LTV по источникам и устройствам')
ax = sns.barplot(x='source_id', y='ltv', palette="ch:1.7", data=source_df)
(source_and_device_df
.pivot_table(index='source_id', columns='device', values='ltv', aggfunc='max') # декоративная ф-я
.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
)
plt.ylabel('LTV, у.е.')
plt.xlabel('Источники')
plt.show()
1 и 2 источники и ПК - вот откуда идут наиболее легко тратящие покупатели, на них стоит повышать бюджеты. Мобильный LTV лишь чуть-чуть превышает ПК в 10 источнике, но в целом там показатель самый низкий.
Напомню, что средний LTV за всё исследуемое время: 7.37 у.е. - столько приносит пользователь в среднем в месяц
Сколько денег потратили? Всего / на каждый источник / по времени
print(f"Всего потрачено за всё исследуемое время: {costs_log['costs'].sum()} у.е.")
costs_by_source = costs_log.groupby('source_id')['costs'].sum().reset_index()
costs_by_source
sns.barplot(x='source_id', y='costs', palette="hls", data=costs_by_source)
plt.title('Затраты по источникам')
plt.ylabel('Затраты')
plt.xlabel('Источники')
plt.show()
На 3 источник потрачено гораздо больше средств, чем на остальные. Интересно, целесообразно ли? Узнаем далее.
costs_log['week'] = costs_log['dt'].astype('datetime64[W]')
costs_log['month'] = costs_log['dt'].astype('datetime64[M]')
costs_log['month'] = costs_log['month'].dt.strftime('%Y-%m')
costs_by_month = costs_log.groupby('month')['costs'].sum().reset_index()
costs_by_month
sns.barplot(x='month', y='costs', palette="rainbow", data=costs_by_month)
plt.title('Затраты по месяцам')
plt.ylabel('Затраты')
plt.xlabel('Месяц')
plt.show()
На протяжении года наибольшие средства были потрачены в холодные месяцы, не удивительно, что трафика на сайте в эти месяцы больше. Т.е. это не только потому, что все вернулись с отпусков и заходят с работы, как я писал ранее. Я думаю, можно даже сравнить трафик и расходы:
ax =(costs_log
.groupby('week')['costs'].sum()
.reset_index()
.plot(x='week', y='costs', title='Затраты по неделям и количество посетителей', grid=True, figsize=(16,8), color='brown', linewidth=4)
)
visits_log_weekly.plot(ax=ax, grid=True, figsize=(16, 8), style='--', color='darkred')
plt.legend(['Затраты по неделям','Количество посетителей'])
plt.ylabel('Затраты')
plt.xlabel('Время')
plt.show()
Вот здесь видно как трафик и расходы коррелируют, практически в точности повторяя друг друга. Выходит почти весь успех - это маркетинг и реклама. Здорово ;)
(costs_log
.query('(source_id != 6) and (source_id != 7)')
.groupby(['week','source_id'])['costs']
.sum()
.reset_index()
.pivot_table(index='week', columns='source_id', values='costs', aggfunc='sum')
.plot(title='Затраты по источникам', grid=True)
)
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 9','Источник 10'])
plt.ylabel('Затраты')
plt.xlabel('Время')
plt.show()
3 источник был самым затратным на протяжении всего года, не помню, чтоб по нему было много трафика или продаж, стоит пересмотреть бюджет и перераспередлить на более выгодные, а на какие узнаем далее
visits_log['month'] = visits_log['month'].dt.strftime('%Y-%m')
costs_log = costs_log.rename(columns = {'dt':'day'})
visits_and_costs = visits_log.merge(costs_log, on=['source_id','day','week','month'])
visits_and_costs.tail()
(visits_and_costs
.query('(source_id != 6) and (source_id != 7)')
.groupby(['device','week'])['costs']
.sum()
.reset_index()
.pivot_table(index='week', columns='device', values='costs', aggfunc='sum')
.plot(title='Затраты по устройствам', grid=True, color=['darkgreen','brown'])
)
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('Затраты')
plt.xlabel('Время')
plt.show()
На ПК больше затрат на протяжении всего года, что вполне логично, ведь оттуда и средние чеки больше, и покупают чаще
source_df = (visits_and_costs
.query('(source_id != 6) and (source_id != 7)')
.groupby(['source_id'])
.agg({'uid':'nunique','costs':'sum'})
.reset_index()
)
source_and_device_df = (visits_and_costs
.query('(source_id != 6) and (source_id != 7)')
.groupby(['source_id','device'])
.agg({'uid':'nunique','costs':'sum'})
.reset_index()
)
ax = sns.barplot(x='source_id', y='costs', palette="ch:1.9", data=source_df)
(source_and_device_df
.pivot_table(index='source_id', columns='device', values='costs', aggfunc='sum')
.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
)
plt.title('Затраты по источникам и устройствам')
plt.ylabel('Затраты * 10*7')
plt.xlabel('Источники')
plt.show()
На 3 источник тратится намного больше денег, чем на другие, при этом трафик (как мы поним) с этого источника не сопоставимо выше, а находится на уровне 4 источника, на который потрачено в 2 раза меньше. Далее рассмотрим, окупаются ли эти затраты, или всё таки стоит пересмотреть бюджет.
Сколько стоило привлечение одного покупателя из каждого источника?
print(f"Итак, средняя стоимость привлечения клиента = {(costs_log['costs'].sum() / orders_log['uid'].nunique()).round(4)} у.е.")
Далее нам нужна таблица в которой будет число только абсолютно уникальных покупателей за каждый день и сколько было потрачено в день их первого визита. Т.е. нам нужны UIDы всех покупателей, отсеять visits_log по ним, а потом сопоставить дни первого визита и дни затрат + сами затраты.
unique_buyers = orders_log['uid'].unique() # список уникальных ID покупателей
visits_of_buyers = visits_log.query('uid in @unique_buyers').reset_index(drop=True) # остались визиты только покупателей
visits_of_buyers = visits_of_buyers.drop_duplicates(subset='uid') # удалили повторные покупки, остались только первые покупки всех покупателей
visits_of_buyers['first_session_day'] = visits_of_buyers['first_session'].astype('datetime64[D]') # взяли день из даты первого визита
source_of_buyers = visits_of_buyers.groupby(['first_session_day','source_id']).agg({'uid':'nunique'}).reset_index() # сагрегировали количество уников по дню и источнику
source_of_buyers.head()
Дальше можем совместить эту таблицу с затратами и посчитать стоимость каждого покупателя по источнику
cac_by_source = source_of_buyers.merge(costs_log, left_on=['first_session_day','source_id'], right_on=['day','source_id'])
cac_by_source['cac'] = (cac_by_source['costs'] / cac_by_source['uid']).round(2)
cac_by_source_grouped = cac_by_source.groupby('source_id').mean().reset_index()
sns.barplot(x='source_id', y='cac', palette="Set2", data=cac_by_source_grouped)
plt.title('Средняя стоимость привлечения клиента по источникам')
plt.ylabel('Средняя стоимость привлечения')
plt.xlabel('Источники')
plt.show()
Наиболее дорогими обходятся клиенты с 3 источника, а самые дешевые с 9-го. Так же к дешевым можно отнести 1,4 и 10 источники. Как маркетолог, могу с большой уверенностью сказать, что 3 источник это медийная реклама, обычно она играет на раскрутку бренда и узнаваемость, а не генерирует трафик и продажи.
Дальше я попытался посчитать стоимость затрат по устройствам, но т.к. такой колонки нет в таблице с затратами, что бы я не делал, ничего не выходило, например вот такая таблица, в которой я высчитал процент покупателей внутри дня по устройствам, потом этот процент применил к затратам и думал получить разный CAC, но потом понял, что так не получится, CAC будет одинаковым для обоих устройств:
# оставляем нужные столбцы и аггрегируем данные по ним
device_of_buyers = (visits_of_buyers
.groupby(['first_session_day','device'])
.agg({'uid':'nunique'})
.reset_index()
)
# объединяем количество ПК и мобильных покупателей
sum_uids = device_of_buyers.groupby('first_session_day')['uid'].sum().reset_index()
sum_uids = sum_uids.rename(columns = {'uid':'sum_uids'})
# объединяем
device_of_buyers = device_of_buyers.merge(sum_uids, on='first_session_day')
# процентные соотношения ко всем устройствам
device_of_buyers['percent_of_all_device'] = (device_of_buyers['uid'] / device_of_buyers['sum_uids'])
# оставляем затраты по дням в costs_log и объединяем
costs_prepare = costs_log.groupby('day')['costs'].sum().reset_index()
device_of_buyers_and_costs = device_of_buyers.merge(costs_prepare, left_on='first_session_day', right_on='day')
# создаем дополнительные столбцы
device_of_buyers_and_costs['costs_by_device'] = (device_of_buyers_and_costs['costs'] * device_of_buyers_and_costs['percent_of_all_device']).round(2)
device_of_buyers_and_costs['cac_by_device'] = (device_of_buyers_and_costs['costs_by_device'] / device_of_buyers_and_costs['uid']).round(2)
device_of_buyers_and_costs.head()
Как видим уже по первым строкам, попытка неудачная, двигаемся дальше
Посмотрим как CAC изменялся с течением времени, по неделям:
buyers_by_days = visits_of_buyers.groupby(['first_session_day']).agg({'uid':'nunique'}).reset_index()
cac_by_days = buyers_by_days.merge(costs_prepare, left_on=['first_session_day'], right_on=['day'])
cac_by_days = cac_by_days.drop(columns='day', axis=1)
cac_by_days['cac'] = (cac_by_days['costs'] / cac_by_days['uid']).round(2)
cac_by_days['first_session_week'] = cac_by_days['first_session_day'].astype('datetime64[W]')
cac_by_week = cac_by_days.groupby('first_session_week')['cac'].mean().reset_index()
cac_by_week.plot(x='first_session_week', y='cac', grid=True, color='teal')
plt.title('Средняя стоимость привлечения клиента')
plt.ylabel('Средняя стоимость клиента, CAC')
plt.xlabel('Время')
plt.show()
Посмотрим ещё по месяцам, в виде таблицы:
cac_by_days['first_session_month'] = cac_by_days['first_session_day'].astype('datetime64[M]')
cac_by_days['first_session_month'] = cac_by_days['first_session_month'].dt.strftime('%Y-%m')
cac_by_month = cac_by_days.groupby('first_session_month')['cac'].mean().reset_index()
cac_by_month
Как видно стоимость закупки клиентов растёт с течением времени. Видимо борьба за внимание каждого пользователя растёт, вместе с ценой. Очень важно тратить эти деньги на более лояльных пользователей, на каких? прочитаете в общем выводе, после исследования ROI
На сколько окупились расходы? (ROI)
Сначала построим график ROI по дням, причём привяжем покупателей не к дням их первой покупки, а дням первого визита, ведь именно тогда на них были потрачены деньги на привлечение, соответственно к этим дням и нужно привязывать расходы на них
# удалим визиты без продаж
visits_by_buyers = visits_and_orders.drop_duplicates(subset=['uid','buy_ts'])
# сгруппируем таблицу и оставим только нужные столбцы с аггрегированными показателями
visits_by_buyers = (visits_by_buyers
.groupby(['first_session','uid'])
.agg({'revenue':'sum'})
.reset_index()
)
visits_by_buyers.head()
Проверим не потеряли ли мы доход при скрещивании таблиц и других опирациях:
print(f"Суммарный доход в новой таблице visits_by_buyers = {visits_by_buyers['revenue'].sum()}, а суммарный доход в исходной, orders_log = {orders_log['revenue'].sum().round(1)}, всё хорошо")
Теперь привяжем расходы:
visits_by_buyers['first_session_day'] = visits_by_buyers['first_session'].astype('datetime64[D]')
#объединяем с таблицей по затратам
main_df = visits_by_buyers.merge(costs_prepare, left_on='first_session_day', right_on='day')
main_df = main_df.drop(columns='day', axis=1)
main_df
Посчитаем LTV, CAC и ROI внутри каждого дня:
# создаем аггрегированную таблицу, оставим только нужные столбцы
roi = (main_df
.groupby(['first_session_day', 'costs'])
.agg({'uid':'nunique','revenue':'sum'})
.reset_index()
)
# добавляем необходимые показатели для подсчёта roi
roi['profit'] = (roi['revenue'] * margin_rate).round(2)
roi['ltv'] = (roi['profit'] / roi['uid']).round(2)
roi['cac'] = (roi['costs'] / roi['uid']).round(2)
roi['roi'] = (roi['ltv'] / roi['cac'] *100).round(2) # можно ещё посчитать так: (roi['profit']/roi['costs']).round(2) - те же самые цифры
roi.head()
# создаем график
roi.plot(x='first_session_day', y='roi', grid=True)
plt.title('Средний ROI по дням')
plt.ylabel('ROI, %')
plt.xlabel('Время')
plt.show()
Этот график интересен тем, что он показывает на сколько выгодными оказались люди, на которых в день их первого посещения потратили деньги. Т.е., например, за 1 день пришло 10 человек за 100 у.е. затрат, потом они в течение года приносили деньги и отбивали эти 100 у.е. и теперь мы видим как они окупились (или нет) на графике в рамках своих дней
На графике видны пики, видимо в эти дни были преведены особо расточительные люди, которые в течение последующих месяцев тратили достаточно много денег и окупили себя в несколько раз, самый большой пик - в 6 раз
Теперь создадим такую же табоицу, но по месяцам, чтобы посмотреть на общую картину
# удалим визиты без продаж
orders_log_clear = orders_log.drop_duplicates(subset=['uid','buy_ts'])
# создаем аггрегированную таблицу, оставим только нужные столбцы
orders_log_clear = (orders_log_clear
.groupby(['first_buy_month'])
.agg({'uid':'nunique','revenue':'sum'})
.reset_index()
)
orders_log_clear['first_buy_month'] = orders_log_clear['first_buy_month'].dt.strftime('%Y-%m') #для красоты
# создаем аггрегированную таблицу затрат по месяцам
costs_by_month = costs_log.groupby('month')['costs'].sum()
# объединяем с продажами
orders_and_costs = orders_log_clear.merge(costs_by_month, left_on='first_buy_month', right_on='month')
# сгруппируем таблицу и оставим только нужные столбцы с аггрегированными показателями
roi_by_month = (orders_and_costs
.groupby(['first_buy_month'])
.agg({'uid':'sum','revenue':'sum','costs':'sum'})
.reset_index()
)
# добавляем необходимые показатели для подсчёта roi
roi_by_month['profit'] = (roi_by_month['revenue'] * margin_rate).round(2)
roi_by_month['ltv'] = (roi_by_month['profit'] / roi_by_month['uid']).round(2)
roi_by_month['cac'] = (roi_by_month['costs'] / roi_by_month['uid']).round(2)
roi_by_month['roi'] = (roi_by_month['ltv'] / roi_by_month['cac'] *100).round(2) # можно ещё посчитать так: (roi2['profit']/roi2['costs'] *100).round(2) - те же самые цифры
roi_by_month
И теперь посмотрим, кака ROI изменяется по месяцам:
# создаем график
roi_by_month.plot(x='first_buy_month', y='roi', grid=True, color='darkorange')
plt.title('Средний ROI по месяцам')
plt.ylabel('ROI,%')
plt.xlabel('Время')
plt.show()
Видно, что те кто пришёл раньше окупил себя лучше, но это только потому, что у них было больше времени на это. К тому же сдесь показан средний ROI. Опираться на этот график не стоит, лучше провести когортный анализ, к которому мы сейчас и приступим
Далее построим кумулятивный ROI по когортам, для начала подготовим таблицу с прибылью по месяцам и когортам:
visits_by_buyers_cohorts = orders_log.groupby(['buy_month','first_buy_month']).agg({'uid':'nunique','revenue':'sum'}).reset_index()
# столбец с возрастом когорт:
visits_by_buyers_cohorts['cohort_month'] = (( visits_by_buyers_cohorts['buy_month'] - visits_by_buyers_cohorts['first_buy_month'] ) / np.timedelta64(1, 'M')).round().astype('int') # месяц/когорта
# сделаем "красивый" месяц, т.е. отбросим день:
visits_by_buyers_cohorts['first_buy_month'] = visits_by_buyers_cohorts['first_buy_month'].dt.strftime('%Y-%m')
# агрегируем данные по когортам (месяцам) и возрастом когорт
visits_by_buyers_cohorts = visits_by_buyers_cohorts.groupby(['first_buy_month','cohort_month']).agg({'uid':'sum','revenue':'sum'}).reset_index()
visits_by_buyers_cohorts
Теперь нужно объеденить с затратами. Помним, что затраты не привязаны к пользователям напрямую, они агрегированы по дням, а для когортного анализа - по мясацам, поэтому мы приписываем все затраты к первому месяцу покупателей (к месяцу когда посетитель совершил первую покупку).
Далее для создания кумулятивного ROI, а его нельзя создать с помощью .cumsum(), получатся ошибочные цифры, я проверял, нужно создать кумулятивных покупателей, т.е. учесть, что они на следующий месяц снова покупали и прибавлять к изначальному числу. И так же кумулятивную прибыль.
P.S. Поверьте, я всё продумал, так - правильно, даже если другие делают по-другому. Конечно в реальной практике чаще всего есть привязка затрат к User ID, и можно посчитать так же затраты на их повторное привлечение, но конкретно в этом проекте приходится мириться с агрегированными данными
roi_cohorts = visits_by_buyers_cohorts.merge(costs_by_month, left_on='first_buy_month', right_on='month')
#создаем будущие столбцы с кумулятивными метриками: покупатели и доход
roi_cohorts['cum_buyers'] = 0
roi_cohorts['cum_revenue'] = 0
#наполняем столбцы
for row in roi_cohorts.index: # для каждой строчки (по индексу) таблицы
if roi_cohorts.loc[row, 'cohort_month'] == 0: #если в строке возраст когорты 0, то берем исходное количество (покупателей/дохода)
roi_cohorts.loc[row, 'cum_buyers'] = roi_cohorts.loc[row, 'uid']
roi_cohorts.loc[row, 'cum_revenue'] = roi_cohorts.loc[row, 'revenue']
else: #иначе прибавляем показатель ячейки покупателей/дохода к сумме предыдущих ячеек
roi_cohorts.loc[row, 'cum_buyers'] = roi_cohorts.loc[row, 'uid'] + roi_cohorts.loc[(row-1), 'cum_buyers']
roi_cohorts.loc[row, 'cum_revenue'] = roi_cohorts.loc[row, 'revenue'] + roi_cohorts.loc[(row-1), 'cum_revenue']
roi_cohorts
И теперь можно считать остальные показатели, LTV, CAC, ROI:
roi_cohorts['cum_profit'] = (roi_cohorts['cum_revenue'] * margin_rate).round(2)
roi_cohorts['cum_ltv'] = (roi_cohorts['cum_profit'] / roi_cohorts['cum_buyers']).round(2)
roi_cohorts['cum_cac'] = (roi_cohorts['costs'] / roi_cohorts['cum_buyers']).round(2)
roi_cohorts['cum_roi'] = (roi_cohorts['cum_ltv'] / roi_cohorts['cum_cac']).round(2) # можно ещё посчитать так: (roi['cum_profit']/roi['costs']).round(2) - те же самые цифры
roi_cohorts
И так как у нас теперь есть уникальные метрики (кумулятивные LTV, CAC и ROI), я выведу их все:
#Когортный анализ кумулятивного LTV
ltv_cohorts_pivot = roi_cohorts.pivot_table(index='first_buy_month', columns='cohort_month', values='cum_ltv').round(2)
plt.figure(figsize=(16, 8.9))
sns.heatmap(ltv_cohorts_pivot, annot=True, fmt='.2f', linewidths=1, linecolor='gray')
plt.title('Кумулятивный LTV по когортам')
plt.ylabel('LTV')
plt.xlabel('Возраст когорты (месяц)')
plt.show()
print('Люди на которых была потрачена сумма на привлечение с течением времени приносят всё больше денег, поэтому средний LTV по когорте растёт')
#Когортный анализ кумулятивного CAC
cac_cohorts_pivot = roi_cohorts.pivot_table(index='first_buy_month', columns='cohort_month', values='cum_cac').round(2)
sns.heatmap(cac_cohorts_pivot, annot=True, fmt='.2f', linewidths=1, linecolor='gray')
plt.title('Кумулятивный CAC по когортам')
plt.ylabel('CAC')
plt.xlabel('Возраст когорты (месяц)')
plt.show()
print('Чем больше люди покупают с течением времени, тем меньше средние затраты на их превлечение, т.е. кумулятивный САС учитывает возврат клиентов')
#Когортный анализ кумулятивного ROI
roi_cohorts_pivot = roi_cohorts.pivot_table(index='first_buy_month', columns='cohort_month', values='cum_roi').round(2)
sns.heatmap(roi_cohorts_pivot, annot=True, fmt='.0%', linewidths=1, linecolor='gray')
plt.title('Кумулятивный ROI по когортам')
plt.ylabel('ROI')
plt.xlabel('Возраст когорты (месяц)')
plt.show()
print(f"ROI по всем пользователям за год = {((roi['profit'].sum() / roi['costs'].sum())):.2%}")
Самая удачная когорта - сентябрьская, она быстро стремится окупить вложения потраченные на них. Но в целом ситуация довольно тяжелая, трафик не окупает себя даже через год. Но возможно риски оправданы в надежде окупить проект через несколько лет. По крайней мере инвесторы кремниевой долины никода не расчитывают получить деньги назад уже через год, там инвестируют по настоящему в долгую. Я думаю здесь примерно такая же история.
Напомню, что маржа установлена как 50%, и этот показатель (если он другой) может кординально поменять картиную.
# создаем аггрегированную таблицу затрат по месяцам и источникам
costs_by_month = costs_log.groupby(['month','source_id'])['costs'].sum().reset_index()
# удалим визиты без продаж
visits_by_buyers = visits_and_orders.drop_duplicates(subset=['uid','buy_ts'])
# сгруппируем таблицу и оставим только нужные столбцы с аггрегированными показателями
visits_by_buyers = (visits_by_buyers
.groupby(['first_buy_month','source_id'])
.agg({'uid':'nunique','revenue':'sum'})
.reset_index()
)
visits_by_buyers['first_buy_month'] = visits_by_buyers['first_buy_month'].dt.strftime('%Y-%m') #для красоты
# объединяем c затратами
roi_by_source = visits_by_buyers.merge(costs_by_month, left_on=['first_buy_month','source_id'], right_on=['month','source_id'])
# добавляем показатели для подсчёта roi
roi_by_source['profit'] = (roi_by_source['revenue'] * margin_rate).round(2)
roi_by_source['ltv'] = (roi_by_source['profit'] / roi_by_source['uid']).round(2)
roi_by_source['cac'] = (roi_by_source['costs'] / roi_by_source['uid']).round(2)
roi_by_source['roi'] = (roi_by_source['ltv'] / roi_by_source['cac'] *100).round(2) # можно ещё посчитать так: (roi['profit']/roi['costs']).round(2) - те же самые цифры
# сводная таблица по устройствам и месяцам
roi_pivot = (roi_by_source
.query('(source_id != 6) and (source_id != 7)') # убираем источники с малым количеством данных
.pivot_table(index='first_buy_month', columns='source_id', values='roi')
)
# создаем график
roi_pivot.plot(grid=True)
plt.title('Средний ROI по источнику')
plt.legend(['Источник 1','Источник 2','Источник 3','Источник 4','Источник 5','Источник 9','Источник 10'])
plt.ylabel('ROI, %')
plt.xlabel('Время')
plt.show()
Видно, что 3 источник - в самом низу. Это значит, что нужно перераспределить бюджет на 1,2 и 9 источники, которые показывают хорошую окупаемость в течение года. Конечно по 9-му - мало трафика, но это как раз потому, что ту и мало денег было потрачено.
# удалим визиты без продаж
visits_by_buyers = visits_and_orders.drop_duplicates(subset=['uid','buy_ts'])
# сгруппируем таблицу и оставим только нужные столбцы с аггрегированными показателями
visits_by_buyers = (visits_by_buyers
.groupby(['first_buy_month','device'])
.agg({'uid':'nunique','revenue':'sum'})
.reset_index()
)
visits_by_buyers['first_buy_month'] = visits_by_buyers['first_buy_month'].dt.strftime('%Y-%m') #для красоты
# объединяем c затратами
roi_by_device = visits_by_buyers.merge(costs_by_month, left_on='first_buy_month', right_on='month')
# добавляем показатели для подсчёта roi
roi_by_device['profit'] = (roi_by_device['revenue'] * margin_rate).round(2)
roi_by_device['ltv'] = (roi_by_device['profit'] / roi_by_device['uid']).round(2)
roi_by_device['cac'] = (roi_by_device['costs'] / roi_by_device['uid']).round(2)
roi_by_device['roi'] = (roi_by_device['ltv'] / roi_by_device['cac'] *100).round(2) # можно ещё посчитать так: (roi['profit']/roi['costs']).round(2) - те же самые цифры
# сводная таблица по устройствам и месяцам
roi_pivot = roi_by_device.pivot_table(index='first_buy_month', columns='device', values='roi')
# создаем график
roi_pivot.plot(grid=True, color=['darkgreen','brown'])
plt.title('Средний ROI по устройствам')
plt.legend(['ПК трафик','Мобильный трафик'])
plt.ylabel('ROI, %')
plt.xlabel('Время')
plt.show()
Здесь можно увидеть, что в холодные месяцы, когда тратилось больше денег - трафик окупался меньше. Об этом нужно помнить всегда, что увеличение затрат не всегда (или даже почти никогда) не приносит такое же увеличение прибыли.
Что касается устройств, то как мы видим они окупаются почти одинаково, лишь ПК возвращает чуть больше денег, чем мобильный трафик, всё таки стоит сделать корректировку ставок по устройствам.
# удалим визиты без продаж
visits_by_buyers = visits_and_orders.drop_duplicates(subset=['uid','buy_ts'])
#сгруппируем таблицу и оставим только нужные столбцы с аггрегированными показателями
visits_by_buyers = (visits_by_buyers
.groupby(['first_buy_month','source_id'])
.agg({'uid':'nunique','revenue':'sum'})
.reset_index()
)
visits_by_buyers['first_buy_month'] = visits_by_buyers['first_buy_month'].dt.strftime('%Y-%m') #для красоты
#объединяем c затратами
roi_by_source = visits_by_buyers.merge(costs_by_month, left_on=['first_buy_month','source_id'], right_on=['month','source_id'])
def funcs(x):
"""
Функция применяет функции к столбцам,
создана для правильного посчёта столбца costs,
в котором нужно сложить только уникальные расходы
"""
first = x['uid'].sum()
second = x['revenue'].sum()
third = x.groupby('first_buy_month')['costs'].first().sum()
return pd.Series([first,second,third], index=['uid','revenue','costs'])
#применяем функцию
roi_by_source = roi_by_source.groupby(['source_id']).apply(funcs).reset_index()
#добавляем показатели для подсчёта roi
roi_by_source['profit'] = (roi_by_source['revenue'] * margin_rate).round(2)
roi_by_source['ltv'] = (roi_by_source['profit'] / roi_by_source['uid']).round(2)
roi_by_source['cac'] = (roi_by_source['costs'] / roi_by_source['uid']).round(2)
roi_by_source['roi'] = (roi_by_source['ltv'] / roi_by_source['cac'] *100).round(2)
roi_by_source
# удалим визиты без продаж
visits_by_buyers = visits_and_orders.drop_duplicates(subset=['uid','buy_ts'])
#сгруппируем таблицу и оставим только нужные столбцы с аггрегированными показателями
visits_by_buyers = (visits_by_buyers
.groupby(['first_buy_month','source_id','device'])
.agg({'uid':'nunique','revenue':'sum'})
.reset_index()
)
visits_by_buyers['first_buy_month'] = visits_by_buyers['first_buy_month'].dt.strftime('%Y-%m') #для красоты
#объединяем c затратами
roi_by_source_and_device = visits_by_buyers.merge(costs_by_month, left_on=['first_buy_month','source_id'], right_on=['month','source_id'])
def funcs(x):
"""
Функция применяет функции к столбцам,
создана для правильного посчёта столбца costs,
в котором нужно сложить только уникальные расходы
"""
first = x['uid'].sum()
second = x['revenue'].sum()
third = x.groupby('first_buy_month')['costs'].first().sum()
return pd.Series([first,second,third], index=['uid','revenue','costs'])
#применяем функцию
roi_by_source_and_device = roi_by_source_and_device.groupby(['source_id','device']).apply(funcs).reset_index()
#добавляем показатели для подсчёта roi
roi_by_source_and_device['profit'] = (roi_by_source_and_device['revenue'] * margin_rate).round(2)
roi_by_source_and_device['ltv'] = (roi_by_source_and_device['profit'] / roi_by_source_and_device['uid']).round(2)
roi_by_source_and_device['cac'] = (roi_by_source_and_device['costs'] / roi_by_source_and_device['uid']).round(2)
roi_by_source_and_device['roi'] = (roi_by_source_and_device['ltv'] / roi_by_source_and_device['cac'] *100).round(2)
roi_by_source_and_device
source_df = roi_by_source.query('(source_id != 6) and (source_id != 7)') # убираем источники с малым количеством данных
source_and_device_df = (roi_by_source_and_device
.query('(source_id != 6) and (source_id != 7)')
.pivot_table(index='source_id', columns='device', values='roi') # сводим по источникам и устройствам
)
# создаем графики
ax = sns.barplot(data=source_df, x='source_id', y='roi', palette="ch:2.8")
source_and_device_df.plot(grid=True, figsize=(16, 8), kind='bar', ax=ax)
plt.title('ROI по источникам и устройствам')
plt.ylabel('ROI, %')
plt.xlabel('Источник')
plt.show()
Наиболее хорошо окупаются 1,2 и 9 источники. С другой стороны 3 источник окупается очень плохо и почему туда тратят так много денег? Не понятно. Нужно срочно пересматривать бюджет.
ПК в целом по всем источникам окупается чуть лучше, поэтому на этот трафик нужно тратить больше, чем на мобильный.
Шаг 3. Вывод
Мы рассмотрели множество графиков и показателей, каждый из них по разному отражает состояние бизнеса, но ключевым всегда будет являтся окупается ли деятельность бизнеса и вложения в него или нет. Вывод я буду делать опираясь непосрдственно на ROI.
По источникам
1 - даёт быстро принимающих решение покупателей, с большим количеством покупок и высоким средним чеком. Таким образом трафик окупается быстро, особенно с ПК. Стоит увеличить бюджет и трудозатраты на маркетинг по этому источнику.
2 - почти так же хорош, как и 1 источник, так же можно увеличить бюджет, но уже на 10-15% меньше, чем в 1 источник. И здесь ещё лучше окупается трафик с ПК, поэтому здесь повышающая корректировка должна быть выше (если такая предусмотрена в настройках рекламных кампаний)
3 - самый плохой источник по окупаемости, затраты нужно сократить раз в 10. Конечно, есть шанс, что он приводит трафик, который потом покупает из других источников, для этого нужно проводить отдельное исследование по аттрибуции (т.е. каким был первый источник трафика у тех, кто в итоге купил с другого источника. Но даже если такие покупатели есть, то это лишь несколько процентов от всего трафика с 3 источника. Этот источник привлекает много нецелевого трафика, который даже не возвращается на сайт в последствии.
4 - тоже не очень хороший источник, следует чуть-чуть сократить бюджет на него, но не так сильно как на 3 источник. Единственные преимущества 3 и 4 источника - люди из них достаточно быстро принимают решения о первой покупке, наверно поэтому туда ошибочно вливается много денег.
5 - этот источник можно не трогать в плане бюджета, хоть и окупается он тоже не быстро, у него хороший показатель LTV, и именно этот источник привёл сентябрьскую когорту людей которые в последствии с других источников покупали много и высокими чеками. Нужно больше уделить внимания именно работе с этим источником, сделать его более качественным, оптимизировать
6 и 7 источники я исключал и рассмотрения, т.к. по нима очень мало данных для того, чтобы делать выводы. Но по ним и небыло затрат, может стоит выделить небольшой бюджет для теста. Возможно это хорошие источники для расширения охвата.
9 - источник окупается лучше всех, при этом затраты на него минимальны и соответственно трафика оттуда мало, деньги освободившиеся от 3 источника стоит потратить сюда. Особенно на ПК-пользователей.
10 - окупается нормально, можно попробывать дополнительно финансировать, для теста. Средняя стоимость привлечения на 9 и 10 новых пользователей источниках довольно низкая, люди из этих источников принимают решение о покупке так же бымтро как с 3 и 4. При этом они очень вовлеченные в изучение сайта.
По устройствам
ПК - сайт отвечает потребностям клиентов, всё хорошо, стоит на данный момент тратить больше бюджета на пользователей с ПК, поставить повышающие коэффицентв к ставкам, если речь идёт о контекстной или таргетированной рекламе
Мобильные - стоит доработать сайт с точки зрения UX|UI пока люди значительно больше предпочитают ПК версию, значит, что пока мобильный сайт не отчечает потребностям покупателей, хоть и в целом трафик с мобильных окупается, всё таки на данный момент деньги вложенные на привлечение ПК-пользователей с большей вероятность окупятся
По времении
Нужно больше тратить денег на привлечение летом, т.к. люди в это время окупаются на данный момент лучше, чем зимние. Кстати именно в теплые месяцы 9 и 10 источники окупаются особенно хорошо
Исключение - когорты сентября и декабря - на них стоит обратить внимание, и потратить больше сил на привлечение именно таких пользователей, это 5 источник, это ПК и самое главное - что и менно привлекло этих пользователей, что было на сайте, какая акция проходила. Если этовозможно - нужно составить портрет этой целевой аудитории
На черную пятницу не стоит вливать слишком много денег, как это было сделано, т.к. на распродажу привлекаются охотники за дешевизной, которые в итоге потом не окупаются, ROI когорты ноября очень низкий.